package ext.tianma.part.util.updateIBAValues;

import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.util.Locale;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;

import ext.tianma.part.processors.ImportBOMProcessors;
import ext.tianma.util.IbaAttributeUtil;
import ext.tianma.util.PropertiesUtil;
import wt.fc.ObjectReference;
import wt.fc.collections.WTArrayList;
import wt.log4j.LogR;
import wt.part.WTPart;
import wt.util.WTException;
import wt.util.WTProperties;

/**
 * 用于查询部件属性信息写入表格
 * @return 下载地址
 * @author laichao_sun
 *
 */
public class WriteToXlsService implements Serializable{
	
	public static String sheetName = "部件属性查询";
	private static final long serialVersionUID = -1832447318994007321L;
	public static String path = "/ptc/Windchill/codebase/ext/tianma/part/config/queryPart.xls";
	public static String configFilePath = "codebase" + File.separator + "ext" + File.separator + "tianma"
			+ File.separator + "cfg" + File.separator + "TianmaPro.properties";
	
	public static String queryPart() {
		System.out.println("SLC===开始查询数据并写入xls==>>");
		WTArrayList partList;
		try {
			partList = ext.tianma.part.util.updateIBAValues.QueryPart.query();
			//System.out.println("SLC===通过配置文件查询到的部件个数==>>" + partList.size());

			// 将按配置文件的查询条件的最新部件写入xls
			String realPath = writeToXls(partList);
			if (realPath == null) {
				throw new WTException("获取下载地址失败!");
			}
			System.out.println("下载地址:" + realPath);
			return realPath;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return "";
	}
	/**
	 * 将按配置文件的查询条件的最新部件写入xls
	 * 
	 * @param partList
	 * @return
	 * @throws IOException
	 */
	private static String writeToXls(WTArrayList partList) throws IOException {
		try {
			System.out.println("SLC======>>部件的个数:" + partList.size());

			Workbook we = null;
			InputStream in = null;
			PropertiesUtil ut = new PropertiesUtil(configFilePath);
			String domain = ut.getValueByKey("system.domain");
			String folder = "PartAttributeExcel";
			WTProperties props = WTProperties.getLocalProperties();
			String filePath = props.getProperty("wt.codebase.location") + File.separator + "temp" + File.separator
					+ folder + File.separator;
			File targerFile = new File(filePath);
			if (!targerFile.exists()) {
				targerFile.mkdir();
			}
			String pathAndName = filePath + "partAttribute.xls";
			String realPath = domain + File.separator + "temp" + File.separator + folder + File.separator
					+ "partAttribute.xls";

			// 1.创建HSSFWorkbook,对应一个excel

			HSSFWorkbook wb = new HSSFWorkbook();

			// 2.在workbook中创建一个sheet，对应excel中的sheet
			HSSFSheet sheet = wb.createSheet(sheetName);

			// 3.在sheet中添加表头第0行
			HSSFRow row = sheet.createRow(0);

			// 4.创建单元格，并设置值表头 设置表头居中
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中

			// 声明列对象
			HSSFCell cell = null;

			// 创建标题
			in = new FileInputStream(path);
			we = WorkbookFactory.create(in);
			if (null == path || path.equals("")) {
				throw new WTException("没有获取到部件查询条件的配置路径，请检查!");
			}
			if (null == in || null == we) {
				throw new WTException("没有获取到部件查询条件的配置路径，请检查!");
			}
			Sheet sheet2 = we.getSheetAt(1);
			String code2 = "";
			String temp2 = "";
			String string = "";
			// 设置第一行的内容
			for (int i = 1; i < sheet2.getLastRowNum() + 1; i++) {
				Row row2 = sheet2.getRow(i);
				temp2 = getCellValue(row2.getCell(0)).trim();
				code2 = getCellValue(row2.getCell(1)).trim();
				cell = row.createCell(i - 1);
				cell.setCellValue(temp2);
				cell.setCellStyle(cellStyle);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				// 调整每一列宽度自适应
				sheet.autoSizeColumn((short) (i - 1));
			}
			// 写入要查询的部件的信息
			for (int j = 0; j < partList.size(); j++) {
				row = sheet.createRow(j + 1);
				ObjectReference partref = (ObjectReference) partList.get(j);
				Object partobj = partref.getObject();
				WTPart part = (WTPart) partobj;
				for (int i = 1; i < sheet2.getLastRowNum() + 1; i++) {
					Row row2 = sheet2.getRow(i);
					code2 = getCellValue(row2.getCell(1)).trim();
					if ("number".equals(code2)) {
						string = part.getNumber();
					} else if("state".equals(code2)){
						string = part.getLifeCycleState().getDisplay(Locale.CHINA);
					}else if("视图".equals(code2)){
						string = part.getViewName();
					} else{
						string = IbaAttributeUtil.getAttributeValue(part, code2);
					}
					cell = row.createCell(i - 1);
					cell.setCellValue(string);
					cell.setCellStyle(cellStyle);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					// 调整每一列宽度自适应
					sheet.autoSizeColumn((short) (i - 1));
				}
				System.out.println("SLC======>>写入表格部件的个数:" + (j+1));
			}
			FileOutputStream os = new FileOutputStream(pathAndName);
			wb.write(os);
			os.flush();
			os.close();
			close(in);
			return realPath;
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (WTException e) {
			e.printStackTrace();
		} finally {

		}
		return null;
	}

	/**
	 * 获取单元格中的信息
	 * 
	 * @param cell 单元格
	 * @return 主要参数信息
	 * @throws Exception
	 */
	public static String getCellValue(Cell cell) throws WTException {
		String value = "";
		try {
			if (null == cell) {
				return "";
			}
			switch (cell.getCellType()) {
			case XSSFCell.CELL_TYPE_NUMERIC:
				value = Double.toString(cell.getNumericCellValue());
				break;
			case XSSFCell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				break;
			case XSSFCell.CELL_TYPE_BLANK:
				value = "";
				break;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				value = Boolean.toString(cell.getBooleanCellValue());
				break;
			case XSSFCell.CELL_TYPE_FORMULA:
				try {
					value = String.valueOf(cell.getNumericCellValue());
				} catch (Exception e) {
					value = cell.getStringCellValue();
				}
				break;
			default:
				break;
			}
		} catch (Exception e) {
		}
		return value.trim();
	}

	/**
	 * 关闭资源
	 * 
	 * @param closeable
	 */
	public static void close(Closeable closeable) {
		try {
			if (closeable != null) {
				closeable.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

}
